CREATE PROCEDURE Gallery_FindAll
AS
BEGIN
    Select 
    GalleryID,
    GroupID,
    Name,
    Picture,
    Description,
    FileUrl,
    Target,
    FileType,
    IsActive,
    DateCreated,
    DateModified,
    SortIndex,
    IsDeleted
    from Gallery
END
GO

CREATE PROCEDURE Gallery_FindById
@GalleryID int
AS
BEGIN
    Select * from Gallery where GalleryID = @GalleryID
END
GO

CREATE PROCEDURE Gallery_FindByName
@name nvarchar(50)
AS
BEGIN
    Select * from Gallery where GalleryID = @name
END
GO

CREATE PROCEDURE Gallery_Insert
        @GalleryID int ,
        @GroupID int ,
        @Name nvarchar (50),
        @Picture varchar (250),
        @Description nvarchar (-1),
        @FileUrl varchar (250),
        @Target varchar (10),
        @FileType varchar (50),
        @IsActive bit ,
        @DateCreated datetime ,
        @DateModified datetime ,
        @SortIndex int ,
        @IsDeleted bit 
AS
BEGIN
INSERT INTO Gallery
(
        GalleryID,
        GroupID,
        Name,
        Picture,
        Description,
        FileUrl,
        Target,
        FileType,
        IsActive,
        DateCreated,
        DateModified,
        SortIndex,
        IsDeleted
)
VALUES
(
        @GalleryID,
        @GroupID,
        @Name,
        @Picture,
        @Description,
        @FileUrl,
        @Target,
        @FileType,
        @IsActive,
        @DateCreated,
        @DateModified,
        @SortIndex,
        @IsDeleted
)
select @GalleryID = @@IDENTITY
END
GO

CREATE PROCEDURE Gallery_Update
        @GalleryID int ,
        @GroupID int ,
        @Name nvarchar (50),  
        @Picture varchar (250),  
        @Description nvarchar (-1),  
        @FileUrl varchar (250),  
        @Target varchar (10),  
        @FileType varchar (50),  
        @IsActive bit ,
        @DateCreated datetime ,
        @DateModified datetime ,
        @SortIndex int ,
        @IsDeleted bit
AS
BEGIN
UPDATE  Gallery SET
    GroupID = @GroupID,
    Name = @Name,
    Picture = @Picture,
    Description = @Description,
    FileUrl = @FileUrl,
    Target = @Target,
    FileType = @FileType,
    IsActive = @IsActive,
    DateCreated = @DateCreated,
    DateModified = @DateModified,
    SortIndex = @SortIndex,
    IsDeleted = @IsDeleted
    WHERE GalleryID = @GalleryID
END
GO

CREATE PROCEDURE Gallery_Delete
    @GalleryID int
AS
BEGIN
    DELETE FROM Gallery where GalleryID = @GalleryID
END
GO

CREATE PROCEDURE Gallery_Paging
    @PageSize int, 
    @PageNumber int
    AS 
    declare @RowStart int
    declare @RowEnd int
    if @PageNumber > 0 
    BEGIN	
    set @PageNumber = @PageNumber - 1
    set @RowStart = @PageSize * @PageNumber + 1;
    set @RowEnd = @RowStart  + @PageSize - 1;
    SELECT TotalRowCount = COUNT(GalleryID) FROM Gallery;
    WITH MyTable as 
    (
    SELECT * ,  ROW_NUMBER() OVER (order by SortIndex) as RowNumber From  Gallery
    )
    SELECT * from MyTable where RowNumber >= @RowStart and RowNumber <= @RowEnd
END
GO

CREATE proc Gallery_SetActive
@GalleryID int
AS
declare @Type bit
SELECT @Type =  IsActive from Gallery where GalleryID = @GalleryID
BEGIN
IF(@Type = 1)
Update Gallery set IsActive = 0 where GalleryID = @GalleryID
else
Update Gallery set IsActive = 1 where GalleryID = @GalleryID
END
GO

CREATE proc Gallery_SetSortIndex
@GalleryID int, 
@Type int
AS
declare @CurrentSort int
select @CurrentSort = SortIndex from  Gallery where GalleryID = @GalleryID
BEGIN
IF(@Type = 1)
Update Gallery set SortIndex =  @CurrentSort + 1 where GalleryID = @GalleryID
else
Update Gallery set SortIndex =  @CurrentSort - 1 where GalleryID = @GalleryID
END
GO

CREATE PROCEDURE Gallery_RemoveSelectedItem
    @GalleryID int
AS
BEGIN
    UPDATE Gallery SET IsDeleted = 1 WHERE GalleryID = @GalleryID
END
GO

CREATE PROCEDURE Gallery_RestoreSelectedItem
    @GalleryID int
AS
BEGIN
    UPDATE Gallery SET IsDeleted = 0 WHERE GalleryID = @GalleryID
END
GO

CREATE PROCEDURE Gallery_RemoveAll
    @GalleryID int
AS
BEGIN
    UPDATE Gallery SET IsDeleted = 1
END
GO

CREATE PROCEDURE Gallery_RestoreAll
    @GalleryID int
AS
BEGIN
    UPDATE Gallery SET IsDeleted = 0
END
GO

